﻿using CommandService;
using Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BLL.custom
{
    /// <summary>
    /// 客户数据源数据库连接方法类
    /// </summary>
  public   class CuDbHelper
    {
        static string ufcon = string.Empty;
        /// <summary>
        /// 与客户数据库对接方法
        /// </summary>
        /// <param name="strsql">执行sql</param>
        /// <param name="baseCode"></param>
        /// <returns></returns>
        public DataTable getData(string strsql, string baseCode)
        {
            getConString(baseCode);
            using (SqlConnection conn = new SqlConnection(ufcon))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = strsql;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable datatable = new DataTable();
                    adapter.Fill(datatable);
                    return datatable;
                }
            }
        }
        private void getConString(string code)
        {
            Commands command = new Commands();
            string sql = "select * from sys_CustomerBase_info where status='Y'";
            if (!string.IsNullOrWhiteSpace(code))
            {
                sql = sql + " and DbCode='" + code + "'";
            }
            var table = command.GetDataTable(sql);
            if (table.Rows.Count > 0)
            {
                ufcon = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};Connect Timeout=30", table.Rows[0]["serverip"].ToString(), table.Rows[0]["baseName"].ToString(), table.Rows[0]["logid"].ToString(), table.Rows[0]["pwd"].ToString());
            }

        }
        #region 多表事物更新方法(仅做添加、删除、修改使用)
        /// <summary>
        /// 多表事物更新方法(仅做添加、删除、修改使用)
        /// </summary>
        /// <param name="cmdlist">执行SQL语句</param>
        /// <param name="parlist">参数集合</param>
        /// <returns></returns>
        public ReturnResult TransUpdate(List<string> cmdlist, List<SqlParameter[]> parlist, string DbName)
        {
            ReturnResult rsl = new ReturnResult();
            try
            {
                getConString(DbName);
                using (SqlConnection connection = new SqlConnection())
                {
                    connection.ConnectionString = ufcon;
                    connection.Open();
                    using (SqlTransaction myTran = connection.BeginTransaction())//创建事物
                    {
                        try
                        {
                            SqlCommand command = new SqlCommand();
                            command.Transaction = myTran;
                            command.Connection = connection;
                            for (int i = 0; i < cmdlist.Count; i++)
                            {
                                command.CommandType = CommandType.Text;
                                command.CommandText = cmdlist[i].ToString();
                                command.Parameters.Clear();//清空参数集合
                                if (parlist != null)
                                {
                                    foreach (SqlParameter parameter in parlist[i])
                                    {//参数赋值
                                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                                        {
                                            parameter.Value = DBNull.Value;
                                        }
                                        command.Parameters.Add(parameter);
                                    }
                                }
                                command.ExecuteNonQuery();
                            }
                            myTran.Commit();
                            rsl.Result = true;
                        }
                        catch (Exception ex)
                        {
                            rsl.Message = ex.Message;
                            myTran.Rollback();
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                rsl.Message = ex.Message;
            }
            return rsl;
        }
        #endregion
    }
}

